﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using AccessCommon;
using BackboneTest.Models;

namespace BackboneTest.DAL
{
    public partial class TB_UserDAL
    {
        SqlDB mydb;
        public TB_UserDAL(string consrt)
        {
            mydb = new SqlDB(consrt, false);
        }
        public TB_UserDAL(SqlDB _mydb)
        {
            mydb = _mydb;
        }
        #region  成员方法

                
                /// <summary>
                /// 增加一条数据
                /// </summary>
                public int Add(TB_User model)
                {
                    StringBuilder strSql = new StringBuilder();
                    strSql.Append("insert into TB_User(");
                    strSql.Append("UserName,LoginID,PWD,Company,Email)");
                    strSql.Append(" values (");
                    strSql.Append("@UserName,@LoginID,@PWD,@Company,@Email)");
                    strSql.Append(";select @@IDENTITY");
                    SqlParameter[] parameters = {
                            new SqlParameter("@UserName", SqlDbType.NVarChar,50),
                            new SqlParameter("@LoginID", SqlDbType.NVarChar,50),
                            new SqlParameter("@PWD", SqlDbType.NVarChar,50),
                            new SqlParameter("@Company", SqlDbType.NVarChar,100),
                            new SqlParameter("@Email", SqlDbType.NVarChar,100)};
                    parameters[0].Value = model.UserName == null ? (object)DBNull.Value : (object)model.UserName; 
                    parameters[1].Value = model.LoginID == null ? (object)DBNull.Value : (object)model.LoginID; 
                    parameters[2].Value = model.PWD == null ? (object)DBNull.Value : (object)model.PWD; 
                    parameters[3].Value = model.Company == null ? (object)DBNull.Value : (object)model.Company; 
                    parameters[4].Value = model.Email == null ? (object)DBNull.Value : (object)model.Email; 
                    object obj = mydb.ExecuteScalar(CommandType.Text, strSql.ToString(), parameters);
                    if (obj == null)
                    {
                        return -1;
                    }
                    else
                    {
                        return ConvertClass.ToInt32(obj);
                    }
                }
                

       


        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(TB_User model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update TB_User set ");
            strSql.Append("UserName=@UserName,");
            strSql.Append("LoginID=@LoginID,");
            strSql.Append("PWD=@PWD,");
            strSql.Append("Company=@Company,");
            strSql.Append("Email=@Email");    
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
                    new SqlParameter("@ID", SqlDbType.Int,4),
                    new SqlParameter("@UserName", SqlDbType.NVarChar,50),
                    new SqlParameter("@LoginID", SqlDbType.NVarChar,50),
                    new SqlParameter("@PWD", SqlDbType.NVarChar,50),
                    new SqlParameter("@Company", SqlDbType.NVarChar,100),
                    new SqlParameter("@Email", SqlDbType.NVarChar,100)};
            parameters[0].Value = model.ID == null ? (object)DBNull.Value : (object)model.ID; 
            parameters[1].Value = model.UserName == null ? (object)DBNull.Value : (object)model.UserName; 
            parameters[2].Value = model.LoginID == null ? (object)DBNull.Value : (object)model.LoginID; 
            parameters[3].Value = model.PWD == null ? (object)DBNull.Value : (object)model.PWD; 
            parameters[4].Value = model.Company == null ? (object)DBNull.Value : (object)model.Company; 
            parameters[5].Value = model.Email == null ? (object)DBNull.Value : (object)model.Email; 

            if (mydb.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters) > -1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from TB_User ");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            if (mydb.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters) > -1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="IDs"></param>
        /// <returns></returns>
        public bool Delete(string IDs)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from TB_User ");
            strSql.AppendFormat(" where ID in ({0}) ", IDs);
            if (mydb.ExecuteNonQuery(CommandType.Text, strSql.ToString()) > -1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

    
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public TB_User GetModel(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ID,UserName,LoginID,PWD,Company,Email from TB_User ");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            TB_User model = new TB_User();
            DataSet ds = mydb.ExecuteDataset(CommandType.Text, strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                model.UserName = ds.Tables[0].Rows[0]["UserName"].ToString();
                model.LoginID = ds.Tables[0].Rows[0]["LoginID"].ToString();
                model.PWD = ds.Tables[0].Rows[0]["PWD"].ToString();
                model.Company = ds.Tables[0].Rows[0]["Company"].ToString();
                model.Email = ds.Tables[0].Rows[0]["Email"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataTable GetList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,UserName,LoginID,PWD,Company,Email ");
            strSql.Append(" FROM  TB_User  ");
            return mydb.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0];
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataTable GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,UserName,LoginID,PWD,Company,Email ");
            strSql.Append(" FROM  TB_User  ");


            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return mydb.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0];
        }
        
        /*
        /// <summary>
       /// 分页列表
       /// </summary>
       /// <param name="TB_UserItem"></param>
       /// <param name="pageSize"></param>
       /// <param name="index"></param>
       /// <param name="Total"></param>
       /// <returns></returns>
       public DataTable GetList(TB_UserItem searchItem, int pageSize, int index, out int Total)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append("select ID,UserName,LoginID,PWD,Company,Email from TB_User   where 1=1  ");
        
           string order = " ID ";
           return mydb.GetPrintTable(strSql.ToString(), order, "*", pageSize, index, out Total);
        }
        /// <summary>
        /// 导入Excel数据
        /// </summary>
        /// <param name="dtExcel"></param>
        /// <returns></returns>
        public bool ExcelToDataBase(DataTable dtExcel)
        {
            mydb.BeginTransaction();
            try
            {
                dtExcel.Rows.RemoveAt(0);
                int colcount = dtExcel.Columns.Count;
                int rowcount = dtExcel.Rows.Count;
            
            
                foreach (DataRow dr in dtExcel.Rows)
                {
                    if (dr["ProjectNo"].ToString() != "")
                    {
                        TB_User model = new TB_User();
        
                        model.UserName = dr["UserName"].ToString();
                        model.LoginID = dr["LoginID"].ToString();
                        model.PWD = dr["PWD"].ToString();
                        model.Company = dr["Company"].ToString();
                        model.Email = dr["Email"].ToString();
                        
                        if (Add(model)==-1)   //插入新记录
                        {
                            mydb.Rollback();
                            return false;
                        }
            
                    }
                }
                mydb.Commit();
                return true;
            
            }
            catch (Exception ex)
            {
                string strex = ex.ToString();
                mydb.Rollback();
                return false;
            }
            finally
            {
                mydb.EndTransaction();
            
            }
        }
        
        */
        
        #endregion  成员方法
    }

}


